SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [ssb].[createTblScr]
	@existingTableName	SYSNAME,				-- Input parameter which will be taking in the existing table name
	@newTableName		SYSNAME			-- chaning the table name to this one in the final create table script
	--@scriptOutput		VARCHAR(8000) OUTPUT	-- this variable will have the final CREATE TABLE ..script in it which can be used in other procedures
AS
SET NOCOUNT ON
/* DECLARATIONS */
DECLARE @sql VARCHAR(8000)						-- Variable will return either with the CREATE TABLE.. script or error message
DECLARE @table VARCHAR(100)            
declare @cols TABLE (datatype VARCHAR(50))  

IF EXISTS (SELECT * FROM Information_Schema.COLUMNS WHERE Table_Name = @existingTableName AND [TABLE_SCHEMA]= 'SSB')           
BEGIN            
        
	INSERT INTO @cols VALUES('bit')          
	INSERT INTO @cols VALUES('binary')          
	INSERT INTO @cols VALUES('bigint')          
	INSERT INTO @cols VALUES('int')          
	INSERT INTO @cols VALUES('float')          
	INSERT INTO @cols VALUES('datetime')          
	INSERT INTO @cols VALUES('text')          
	INSERT INTO @cols VALUES('image')          
	INSERT INTO @cols VALUES('uniqueidentifier')          
	INSERT INTO @cols VALUES('smalldatetime')          
	INSERT INTO @cols VALUES('tinyint')          
	INSERT INTO @cols VALUES('smallint')          
	INSERT INTO @cols VALUES('sql_variant')          
	           
  SET @sql = ''            
  SELECT
        @sql = @sql + CASE WHEN CHARINDEX('(', @sql, 1) <= 0 THEN '('
                           ELSE ''
                      END + Column_Name + ' ' + Data_Type
        + CASE WHEN Data_Type IN (SELECT
                                        datatype
                                    FROM
                                        @cols) THEN ''
               ELSE '('
          END
        + CASE WHEN data_type IN ('real', 'money', 'decimal', 'numeric')
               THEN CAST(ISNULL(numeric_precision, '') AS VARCHAR) + ','
                    + CASE WHEN data_type IN ('real', 'money', 'decimal',
                                              'numeric')
                           THEN CAST(ISNULL(Numeric_Scale, '') AS VARCHAR)
                      END
               WHEN data_type IN ('char', 'nvarchar', 'VARCHAR', 'nchar')
               THEN CAST(ISNULL(Character_Maximum_Length, '') AS VARCHAR)
               ELSE ''
          END + CASE WHEN Data_Type IN (SELECT
                                                datatype
                                            FROM
                                                @cols) THEN ''
                     ELSE ')'
                END + CASE WHEN Is_Nullable = 'No' THEN ' Not null,'
                           ELSE ' null,'
                      END
    FROM
        Information_Schema.COLUMNS
    WHERE
        Table_Name = @existingTableName      
				AND [TABLE_SCHEMA] = 'SSB'      
	 
	  
  SELECT
        @table = 'CREATE TABLE SSB.' + @newTableName
    FROM
        Information_Schema.COLUMNS
    WHERE
        table_Name = @existingTableName
  SELECT
        @sql = @table + SUBSTRING(@sql, 1, LEN(@sql) - 1) + ' )'
  -- Adding the ending bracket in to the code and assigning it to the return variable
	--SELECT @sql AS DDL
	--RETURN @sql
	--EXEC 
  --SET @scriptOutput = @sql
  SELECT @sql
END           

ELSE
BEGIN        
	SET @sql = 'The table ' + @existingTableName + ' does not exist.'    
	--SET @scriptOutput = @sql
	SELECT  @sql

END
GO
